{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## import useful packages"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "import numpy as np\n",
    "\n",
    "import datetime\n",
    "\n",
    "from scipy.stats import spearmanr\n",
    "\n",
    "import matplotlib.pyplot as plt\n",
    "import seaborn as sns\n",
    "sns.set_style(\"white\")\n",
    "from matplotlib_venn import venn2\n",
    "\n",
    "%matplotlib inline\n",
    "\n",
    "from pydqc.data_consist import numeric_consist_pretty"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## assign values"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "#the data table (pandas DataFrame)\n",
    "table1 =\n",
    "table2 =\n",
    "print(\"table1 size: \" + str(table1.shape))\n",
    "print(\"table2 size: \" + str(table2.shape))\n",
    "\n",
    "key1 =\n",
    "key2 =\n",
    "\n",
    "#global values\n",
    "TABLE1_DARK = \"#4BACC6\"\n",
    "TABLE1_LIGHT = \"#DAEEF3\"\n",
    "TABLE2_DARK = \"#F79646\"\n",
    "TABLE2_LIGHT = \"#FDE9D9\"\n",
    "\n",
    "#get date of today\n",
    "snapshot_date_now = str(datetime.datetime.now().date())\n",
    "print(\"date of today: \" + snapshot_date_now)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## error columns\n",
    "\n",
    "**decktypeid:** exclude<br>**hashottuborspa:** exclude<br>**poolcnt:** exclude<br>**pooltypeid10:** exclude<br>**pooltypeid2:** exclude<br>**pooltypeid7:** exclude<br>**storytypeid:** exclude<br>**fireplaceflag:** exclude<br>**taxdelinquencyflag:** exclude<br>"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## parcelid (type: key)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "col = \"parcelid\"\n",
    "\n",
    "df1 = table1[[col]].copy()\n",
    "df2 = table2[[col]].copy()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### compare intersection"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "#nan_rate\n",
    "nan_rate1 = df1[df1[col].isnull()].shape[0] * 1.0 / df1.shape[0]\n",
    "nan_rate2 = df2[df2[col].isnull()].shape[0] * 1.0 / df2.shape[0]\n",
    "print(\"table1 nan_rate: \" + str(nan_rate1) + \"; table2 nan_rate: \" + str(nan_rate2))\n",
    "\n",
    "set_df1_col = set(df1[col].dropna().values) if nan_rate1 < 1 else set()\n",
    "set_df2_col = set(df2[col].dropna().values) if nan_rate2 < 1 else set()\n",
    "col_overlap = len(set_df1_col.intersection(set_df2_col))\n",
    "col_only_df1, col_only_df2 = len(set_df1_col - set_df2_col), len(set_df2_col - set_df1_col)\n",
    "\n",
    "print(\"col_overlap: \" + str(col_overlap) + \"; col_only_df1: \" + str(col_only_df1) + \"; col_only_df2: \" + str(col_only_df2))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### draw venn graph"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "plt.figure(figsize=(10, 5))\n",
    "venn2([set_df1_col, set_df2_col], set_labels=[\"table1\", \"table2\"], set_colors=(\"#4BACC6\", \"#F79646\"), alpha=0.8)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## airconditioningtypeid (type: str)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "col = \"airconditioningtypeid\"\n",
    "\n",
    "df1 = table1[[key1, col]].copy()\n",
    "df2 = table2[[key2, col]].copy()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### check pairwise consistency"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# merge 2 tables\n",
    "df = df1.merge(df2, left_on=key1, right_on=key2, how=\"inner\")\n",
    "\n",
    "# calculate consistency\n",
    "df['diff_temp'] = df.apply(lambda x: \"Same\" if x['%s_x' %(col)] == x['%s_y' %(col)] else \"Diff\", axis=1)\n",
    "df['diff_temp'] = df.apply(lambda x: \"Same\" if (str(x['%s_x' % (col)]) == 'nan' and str(x['%s_y' % (col)]) == 'nan') else x['diff_temp'], axis=1)\n",
    "\n",
    "corr = df[df[\"diff_temp\"] == \"Same\"].shape[0] * 1.0 / df.shape[0]\n",
    "print(\"consistency rate: \" + str(corr))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## architecturalstyletypeid (type: str)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "col = \"architecturalstyletypeid\"\n",
    "\n",
    "df1 = table1[[key1, col]].copy()\n",
    "df2 = table2[[key2, col]].copy()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### check pairwise consistency"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# merge 2 tables\n",
    "df = df1.merge(df2, left_on=key1, right_on=key2, how=\"inner\")\n",
    "\n",
    "# calculate consistency\n",
    "df['diff_temp'] = df.apply(lambda x: \"Same\" if x['%s_x' %(col)] == x['%s_y' %(col)] else \"Diff\", axis=1)\n",
    "df['diff_temp'] = df.apply(lambda x: \"Same\" if (str(x['%s_x' % (col)]) == 'nan' and str(x['%s_y' % (col)]) == 'nan') else x['diff_temp'], axis=1)\n",
    "\n",
    "corr = df[df[\"diff_temp\"] == \"Same\"].shape[0] * 1.0 / df.shape[0]\n",
    "print(\"consistency rate: \" + str(corr))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## basementsqft (type: numeric)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "col = \"basementsqft\"\n",
    "\n",
    "df1 = table1[[key1, col]].copy()\n",
    "df2 = table2[[key2, col]].copy()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### check pairwise consistency"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# merge 2 tables\n",
    "df = df1.merge(df2, left_on=key1, right_on=key2, how=\"inner\")\n",
    "df = df.dropna(how='any', subset=[col + \"_x\", col + \"_y\"]).reset_index(drop=True)\n",
    "\n",
    "corr = round(spearmanr(df[col + \"_x\"].values, df[col + \"_y\"].values)[0], 3)\n",
    "print(\"consistency rate: \" + str(corr))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### draw consistency graph"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# prepare data\n",
    "df[\"diff_temp\"] = df[col + \"_y\"] - df[col + \"_x\"]\n",
    "draw_values = df[\"diff_temp\"].dropna().values\n",
    "\n",
    "both_min = np.min([df[col + \"_x\"].min(), df[col + \"_y\"].min()])\n",
    "both_max = np.max([df[col + \"_x\"].max(), df[col + \"_y\"].max()])\n",
    "\n",
    "# draw\n",
    "plt.figure(figsize=(12, 6))\n",
    "plt.subplot(121)\n",
    "plt.scatter(df[col + \"_x\"].values, df[col + \"_y\"].values, c=TABLE1_DARK, s=5)\n",
    "plt.plot([both_min, both_max], [both_min, both_max], \"--\", c=\"#bbbbbb\")\n",
    "plt.xlim(both_min, both_max)\n",
    "plt.ylim(both_min, both_max)\n",
    "plt.title(\"corr: %.3f\" %(corr))\n",
    "\n",
    "plt.subplot(122)\n",
    "sns.distplot(draw_values, color=TABLE2_DARK)\n",
    "plt.title(\"Distribution of differences\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "#you can also use the build-in draw function\n",
    "numeric_consist_pretty(df1, df2, key1, key2, col)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## bathroomcnt (type: numeric)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "col = \"bathroomcnt\"\n",
    "\n",
    "df1 = table1[[key1, col]].copy()\n",
    "df2 = table2[[key2, col]].copy()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### check pairwise consistency"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# merge 2 tables\n",
    "df = df1.merge(df2, left_on=key1, right_on=key2, how=\"inner\")\n",
    "df = df.dropna(how='any', subset=[col + \"_x\", col + \"_y\"]).reset_index(drop=True)\n",
    "\n",
    "corr = round(spearmanr(df[col + \"_x\"].values, df[col + \"_y\"].values)[0], 3)\n",
    "print(\"consistency rate: \" + str(corr))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### draw consistency graph"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# prepare data\n",
    "df[\"diff_temp\"] = df[col + \"_y\"] - df[col + \"_x\"]\n",
    "draw_values = df[\"diff_temp\"].dropna().values\n",
    "\n",
    "both_min = np.min([df[col + \"_x\"].min(), df[col + \"_y\"].min()])\n",
    "both_max = np.max([df[col + \"_x\"].max(), df[col + \"_y\"].max()])\n",
    "\n",
    "# draw\n",
    "plt.figure(figsize=(12, 6))\n",
    "plt.subplot(121)\n",
    "plt.scatter(df[col + \"_x\"].values, df[col + \"_y\"].values, c=TABLE1_DARK, s=5)\n",
    "plt.plot([both_min, both_max], [both_min, both_max], \"--\", c=\"#bbbbbb\")\n",
    "plt.xlim(both_min, both_max)\n",
    "plt.ylim(both_min, both_max)\n",
    "plt.title(\"corr: %.3f\" %(corr))\n",
    "\n",
    "plt.subplot(122)\n",
    "sns.distplot(draw_values, color=TABLE2_DARK)\n",
    "plt.title(\"Distribution of differences\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "#you can also use the build-in draw function\n",
    "numeric_consist_pretty(df1, df2, key1, key2, col)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## bedroomcnt (type: numeric)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "col = \"bedroomcnt\"\n",
    "\n",
    "df1 = table1[[key1, col]].copy()\n",
    "df2 = table2[[key2, col]].copy()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### check pairwise consistency"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# merge 2 tables\n",
    "df = df1.merge(df2, left_on=key1, right_on=key2, how=\"inner\")\n",
    "df = df.dropna(how='any', subset=[col + \"_x\", col + \"_y\"]).reset_index(drop=True)\n",
    "\n",
    "corr = round(spearmanr(df[col + \"_x\"].values, df[col + \"_y\"].values)[0], 3)\n",
    "print(\"consistency rate: \" + str(corr))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### draw consistency graph"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# prepare data\n",
    "df[\"diff_temp\"] = df[col + \"_y\"] - df[col + \"_x\"]\n",
    "draw_values = df[\"diff_temp\"].dropna().values\n",
    "\n",
    "both_min = np.min([df[col + \"_x\"].min(), df[col + \"_y\"].min()])\n",
    "both_max = np.max([df[col + \"_x\"].max(), df[col + \"_y\"].max()])\n",
    "\n",
    "# draw\n",
    "plt.figure(figsize=(12, 6))\n",
    "plt.subplot(121)\n",
    "plt.scatter(df[col + \"_x\"].values, df[col + \"_y\"].values, c=TABLE1_DARK, s=5)\n",
    "plt.plot([both_min, both_max], [both_min, both_max], \"--\", c=\"#bbbbbb\")\n",
    "plt.xlim(both_min, both_max)\n",
    "plt.ylim(both_min, both_max)\n",
    "plt.title(\"corr: %.3f\" %(corr))\n",
    "\n",
    "plt.subplot(122)\n",
    "sns.distplot(draw_values, color=TABLE2_DARK)\n",
    "plt.title(\"Distribution of differences\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "#you can also use the build-in draw function\n",
    "numeric_consist_pretty(df1, df2, key1, key2, col)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## buildingclasstypeid (type: str)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "col = \"buildingclasstypeid\"\n",
    "\n",
    "df1 = table1[[key1, col]].copy()\n",
    "df2 = table2[[key2, col]].copy()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### check pairwise consistency"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# merge 2 tables\n",
    "df = df1.merge(df2, left_on=key1, right_on=key2, how=\"inner\")\n",
    "\n",
    "# calculate consistency\n",
    "df['diff_temp'] = df.apply(lambda x: \"Same\" if x['%s_x' %(col)] == x['%s_y' %(col)] else \"Diff\", axis=1)\n",
    "df['diff_temp'] = df.apply(lambda x: \"Same\" if (str(x['%s_x' % (col)]) == 'nan' and str(x['%s_y' % (col)]) == 'nan') else x['diff_temp'], axis=1)\n",
    "\n",
    "corr = df[df[\"diff_temp\"] == \"Same\"].shape[0] * 1.0 / df.shape[0]\n",
    "print(\"consistency rate: \" + str(corr))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## buildingqualitytypeid (type: str)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "col = \"buildingqualitytypeid\"\n",
    "\n",
    "df1 = table1[[key1, col]].copy()\n",
    "df2 = table2[[key2, col]].copy()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### check pairwise consistency"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# merge 2 tables\n",
    "df = df1.merge(df2, left_on=key1, right_on=key2, how=\"inner\")\n",
    "\n",
    "# calculate consistency\n",
    "df['diff_temp'] = df.apply(lambda x: \"Same\" if x['%s_x' %(col)] == x['%s_y' %(col)] else \"Diff\", axis=1)\n",
    "df['diff_temp'] = df.apply(lambda x: \"Same\" if (str(x['%s_x' % (col)]) == 'nan' and str(x['%s_y' % (col)]) == 'nan') else x['diff_temp'], axis=1)\n",
    "\n",
    "corr = df[df[\"diff_temp\"] == \"Same\"].shape[0] * 1.0 / df.shape[0]\n",
    "print(\"consistency rate: \" + str(corr))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## calculatedbathnbr (type: numeric)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "col = \"calculatedbathnbr\"\n",
    "\n",
    "df1 = table1[[key1, col]].copy()\n",
    "df2 = table2[[key2, col]].copy()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### check pairwise consistency"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# merge 2 tables\n",
    "df = df1.merge(df2, left_on=key1, right_on=key2, how=\"inner\")\n",
    "df = df.dropna(how='any', subset=[col + \"_x\", col + \"_y\"]).reset_index(drop=True)\n",
    "\n",
    "corr = round(spearmanr(df[col + \"_x\"].values, df[col + \"_y\"].values)[0], 3)\n",
    "print(\"consistency rate: \" + str(corr))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### draw consistency graph"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# prepare data\n",
    "df[\"diff_temp\"] = df[col + \"_y\"] - df[col + \"_x\"]\n",
    "draw_values = df[\"diff_temp\"].dropna().values\n",
    "\n",
    "both_min = np.min([df[col + \"_x\"].min(), df[col + \"_y\"].min()])\n",
    "both_max = np.max([df[col + \"_x\"].max(), df[col + \"_y\"].max()])\n",
    "\n",
    "# draw\n",
    "plt.figure(figsize=(12, 6))\n",
    "plt.subplot(121)\n",
    "plt.scatter(df[col + \"_x\"].values, df[col + \"_y\"].values, c=TABLE1_DARK, s=5)\n",
    "plt.plot([both_min, both_max], [both_min, both_max], \"--\", c=\"#bbbbbb\")\n",
    "plt.xlim(both_min, both_max)\n",
    "plt.ylim(both_min, both_max)\n",
    "plt.title(\"corr: %.3f\" %(corr))\n",
    "\n",
    "plt.subplot(122)\n",
    "sns.distplot(draw_values, color=TABLE2_DARK)\n",
    "plt.title(\"Distribution of differences\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "#you can also use the build-in draw function\n",
    "numeric_consist_pretty(df1, df2, key1, key2, col)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## finishedfloor1squarefeet (type: numeric)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "col = \"finishedfloor1squarefeet\"\n",
    "\n",
    "df1 = table1[[key1, col]].copy()\n",
    "df2 = table2[[key2, col]].copy()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### check pairwise consistency"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# merge 2 tables\n",
    "df = df1.merge(df2, left_on=key1, right_on=key2, how=\"inner\")\n",
    "df = df.dropna(how='any', subset=[col + \"_x\", col + \"_y\"]).reset_index(drop=True)\n",
    "\n",
    "corr = round(spearmanr(df[col + \"_x\"].values, df[col + \"_y\"].values)[0], 3)\n",
    "print(\"consistency rate: \" + str(corr))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### draw consistency graph"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# prepare data\n",
    "df[\"diff_temp\"] = df[col + \"_y\"] - df[col + \"_x\"]\n",
    "draw_values = df[\"diff_temp\"].dropna().values\n",
    "\n",
    "both_min = np.min([df[col + \"_x\"].min(), df[col + \"_y\"].min()])\n",
    "both_max = np.max([df[col + \"_x\"].max(), df[col + \"_y\"].max()])\n",
    "\n",
    "# draw\n",
    "plt.figure(figsize=(12, 6))\n",
    "plt.subplot(121)\n",
    "plt.scatter(df[col + \"_x\"].values, df[col + \"_y\"].values, c=TABLE1_DARK, s=5)\n",
    "plt.plot([both_min, both_max], [both_min, both_max], \"--\", c=\"#bbbbbb\")\n",
    "plt.xlim(both_min, both_max)\n",
    "plt.ylim(both_min, both_max)\n",
    "plt.title(\"corr: %.3f\" %(corr))\n",
    "\n",
    "plt.subplot(122)\n",
    "sns.distplot(draw_values, color=TABLE2_DARK)\n",
    "plt.title(\"Distribution of differences\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "#you can also use the build-in draw function\n",
    "numeric_consist_pretty(df1, df2, key1, key2, col)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## calculatedfinishedsquarefeet (type: numeric)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "col = \"calculatedfinishedsquarefeet\"\n",
    "\n",
    "df1 = table1[[key1, col]].copy()\n",
    "df2 = table2[[key2, col]].copy()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### check pairwise consistency"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# merge 2 tables\n",
    "df = df1.merge(df2, left_on=key1, right_on=key2, how=\"inner\")\n",
    "df = df.dropna(how='any', subset=[col + \"_x\", col + \"_y\"]).reset_index(drop=True)\n",
    "\n",
    "corr = round(spearmanr(df[col + \"_x\"].values, df[col + \"_y\"].values)[0], 3)\n",
    "print(\"consistency rate: \" + str(corr))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### draw consistency graph"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# prepare data\n",
    "df[\"diff_temp\"] = df[col + \"_y\"] - df[col + \"_x\"]\n",
    "draw_values = df[\"diff_temp\"].dropna().values\n",
    "\n",
    "both_min = np.min([df[col + \"_x\"].min(), df[col + \"_y\"].min()])\n",
    "both_max = np.max([df[col + \"_x\"].max(), df[col + \"_y\"].max()])\n",
    "\n",
    "# draw\n",
    "plt.figure(figsize=(12, 6))\n",
    "plt.subplot(121)\n",
    "plt.scatter(df[col + \"_x\"].values, df[col + \"_y\"].values, c=TABLE1_DARK, s=5)\n",
    "plt.plot([both_min, both_max], [both_min, both_max], \"--\", c=\"#bbbbbb\")\n",
    "plt.xlim(both_min, both_max)\n",
    "plt.ylim(both_min, both_max)\n",
    "plt.title(\"corr: %.3f\" %(corr))\n",
    "\n",
    "plt.subplot(122)\n",
    "sns.distplot(draw_values, color=TABLE2_DARK)\n",
    "plt.title(\"Distribution of differences\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "#you can also use the build-in draw function\n",
    "numeric_consist_pretty(df1, df2, key1, key2, col)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## finishedsquarefeet12 (type: numeric)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "col = \"finishedsquarefeet12\"\n",
    "\n",
    "df1 = table1[[key1, col]].copy()\n",
    "df2 = table2[[key2, col]].copy()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### check pairwise consistency"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# merge 2 tables\n",
    "df = df1.merge(df2, left_on=key1, right_on=key2, how=\"inner\")\n",
    "df = df.dropna(how='any', subset=[col + \"_x\", col + \"_y\"]).reset_index(drop=True)\n",
    "\n",
    "corr = round(spearmanr(df[col + \"_x\"].values, df[col + \"_y\"].values)[0], 3)\n",
    "print(\"consistency rate: \" + str(corr))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### draw consistency graph"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# prepare data\n",
    "df[\"diff_temp\"] = df[col + \"_y\"] - df[col + \"_x\"]\n",
    "draw_values = df[\"diff_temp\"].dropna().values\n",
    "\n",
    "both_min = np.min([df[col + \"_x\"].min(), df[col + \"_y\"].min()])\n",
    "both_max = np.max([df[col + \"_x\"].max(), df[col + \"_y\"].max()])\n",
    "\n",
    "# draw\n",
    "plt.figure(figsize=(12, 6))\n",
    "plt.subplot(121)\n",
    "plt.scatter(df[col + \"_x\"].values, df[col + \"_y\"].values, c=TABLE1_DARK, s=5)\n",
    "plt.plot([both_min, both_max], [both_min, both_max], \"--\", c=\"#bbbbbb\")\n",
    "plt.xlim(both_min, both_max)\n",
    "plt.ylim(both_min, both_max)\n",
    "plt.title(\"corr: %.3f\" %(corr))\n",
    "\n",
    "plt.subplot(122)\n",
    "sns.distplot(draw_values, color=TABLE2_DARK)\n",
    "plt.title(\"Distribution of differences\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "#you can also use the build-in draw function\n",
    "numeric_consist_pretty(df1, df2, key1, key2, col)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## finishedsquarefeet13 (type: numeric)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "col = \"finishedsquarefeet13\"\n",
    "\n",
    "df1 = table1[[key1, col]].copy()\n",
    "df2 = table2[[key2, col]].copy()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### check pairwise consistency"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# merge 2 tables\n",
    "df = df1.merge(df2, left_on=key1, right_on=key2, how=\"inner\")\n",
    "df = df.dropna(how='any', subset=[col + \"_x\", col + \"_y\"]).reset_index(drop=True)\n",
    "\n",
    "corr = round(spearmanr(df[col + \"_x\"].values, df[col + \"_y\"].values)[0], 3)\n",
    "print(\"consistency rate: \" + str(corr))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### draw consistency graph"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# prepare data\n",
    "df[\"diff_temp\"] = df[col + \"_y\"] - df[col + \"_x\"]\n",
    "draw_values = df[\"diff_temp\"].dropna().values\n",
    "\n",
    "both_min = np.min([df[col + \"_x\"].min(), df[col + \"_y\"].min()])\n",
    "both_max = np.max([df[col + \"_x\"].max(), df[col + \"_y\"].max()])\n",
    "\n",
    "# draw\n",
    "plt.figure(figsize=(12, 6))\n",
    "plt.subplot(121)\n",
    "plt.scatter(df[col + \"_x\"].values, df[col + \"_y\"].values, c=TABLE1_DARK, s=5)\n",
    "plt.plot([both_min, both_max], [both_min, both_max], \"--\", c=\"#bbbbbb\")\n",
    "plt.xlim(both_min, both_max)\n",
    "plt.ylim(both_min, both_max)\n",
    "plt.title(\"corr: %.3f\" %(corr))\n",
    "\n",
    "plt.subplot(122)\n",
    "sns.distplot(draw_values, color=TABLE2_DARK)\n",
    "plt.title(\"Distribution of differences\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "#you can also use the build-in draw function\n",
    "numeric_consist_pretty(df1, df2, key1, key2, col)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## finishedsquarefeet15 (type: numeric)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "col = \"finishedsquarefeet15\"\n",
    "\n",
    "df1 = table1[[key1, col]].copy()\n",
    "df2 = table2[[key2, col]].copy()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### check pairwise consistency"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# merge 2 tables\n",
    "df = df1.merge(df2, left_on=key1, right_on=key2, how=\"inner\")\n",
    "df = df.dropna(how='any', subset=[col + \"_x\", col + \"_y\"]).reset_index(drop=True)\n",
    "\n",
    "corr = round(spearmanr(df[col + \"_x\"].values, df[col + \"_y\"].values)[0], 3)\n",
    "print(\"consistency rate: \" + str(corr))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### draw consistency graph"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# prepare data\n",
    "df[\"diff_temp\"] = df[col + \"_y\"] - df[col + \"_x\"]\n",
    "draw_values = df[\"diff_temp\"].dropna().values\n",
    "\n",
    "both_min = np.min([df[col + \"_x\"].min(), df[col + \"_y\"].min()])\n",
    "both_max = np.max([df[col + \"_x\"].max(), df[col + \"_y\"].max()])\n",
    "\n",
    "# draw\n",
    "plt.figure(figsize=(12, 6))\n",
    "plt.subplot(121)\n",
    "plt.scatter(df[col + \"_x\"].values, df[col + \"_y\"].values, c=TABLE1_DARK, s=5)\n",
    "plt.plot([both_min, both_max], [both_min, both_max], \"--\", c=\"#bbbbbb\")\n",
    "plt.xlim(both_min, both_max)\n",
    "plt.ylim(both_min, both_max)\n",
    "plt.title(\"corr: %.3f\" %(corr))\n",
    "\n",
    "plt.subplot(122)\n",
    "sns.distplot(draw_values, color=TABLE2_DARK)\n",
    "plt.title(\"Distribution of differences\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "#you can also use the build-in draw function\n",
    "numeric_consist_pretty(df1, df2, key1, key2, col)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## finishedsquarefeet50 (type: numeric)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "col = \"finishedsquarefeet50\"\n",
    "\n",
    "df1 = table1[[key1, col]].copy()\n",
    "df2 = table2[[key2, col]].copy()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### check pairwise consistency"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# merge 2 tables\n",
    "df = df1.merge(df2, left_on=key1, right_on=key2, how=\"inner\")\n",
    "df = df.dropna(how='any', subset=[col + \"_x\", col + \"_y\"]).reset_index(drop=True)\n",
    "\n",
    "corr = round(spearmanr(df[col + \"_x\"].values, df[col + \"_y\"].values)[0], 3)\n",
    "print(\"consistency rate: \" + str(corr))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### draw consistency graph"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# prepare data\n",
    "df[\"diff_temp\"] = df[col + \"_y\"] - df[col + \"_x\"]\n",
    "draw_values = df[\"diff_temp\"].dropna().values\n",
    "\n",
    "both_min = np.min([df[col + \"_x\"].min(), df[col + \"_y\"].min()])\n",
    "both_max = np.max([df[col + \"_x\"].max(), df[col + \"_y\"].max()])\n",
    "\n",
    "# draw\n",
    "plt.figure(figsize=(12, 6))\n",
    "plt.subplot(121)\n",
    "plt.scatter(df[col + \"_x\"].values, df[col + \"_y\"].values, c=TABLE1_DARK, s=5)\n",
    "plt.plot([both_min, both_max], [both_min, both_max], \"--\", c=\"#bbbbbb\")\n",
    "plt.xlim(both_min, both_max)\n",
    "plt.ylim(both_min, both_max)\n",
    "plt.title(\"corr: %.3f\" %(corr))\n",
    "\n",
    "plt.subplot(122)\n",
    "sns.distplot(draw_values, color=TABLE2_DARK)\n",
    "plt.title(\"Distribution of differences\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "#you can also use the build-in draw function\n",
    "numeric_consist_pretty(df1, df2, key1, key2, col)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## finishedsquarefeet6 (type: numeric)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "col = \"finishedsquarefeet6\"\n",
    "\n",
    "df1 = table1[[key1, col]].copy()\n",
    "df2 = table2[[key2, col]].copy()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### check pairwise consistency"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# merge 2 tables\n",
    "df = df1.merge(df2, left_on=key1, right_on=key2, how=\"inner\")\n",
    "df = df.dropna(how='any', subset=[col + \"_x\", col + \"_y\"]).reset_index(drop=True)\n",
    "\n",
    "corr = round(spearmanr(df[col + \"_x\"].values, df[col + \"_y\"].values)[0], 3)\n",
    "print(\"consistency rate: \" + str(corr))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### draw consistency graph"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# prepare data\n",
    "df[\"diff_temp\"] = df[col + \"_y\"] - df[col + \"_x\"]\n",
    "draw_values = df[\"diff_temp\"].dropna().values\n",
    "\n",
    "both_min = np.min([df[col + \"_x\"].min(), df[col + \"_y\"].min()])\n",
    "both_max = np.max([df[col + \"_x\"].max(), df[col + \"_y\"].max()])\n",
    "\n",
    "# draw\n",
    "plt.figure(figsize=(12, 6))\n",
    "plt.subplot(121)\n",
    "plt.scatter(df[col + \"_x\"].values, df[col + \"_y\"].values, c=TABLE1_DARK, s=5)\n",
    "plt.plot([both_min, both_max], [both_min, both_max], \"--\", c=\"#bbbbbb\")\n",
    "plt.xlim(both_min, both_max)\n",
    "plt.ylim(both_min, both_max)\n",
    "plt.title(\"corr: %.3f\" %(corr))\n",
    "\n",
    "plt.subplot(122)\n",
    "sns.distplot(draw_values, color=TABLE2_DARK)\n",
    "plt.title(\"Distribution of differences\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "#you can also use the build-in draw function\n",
    "numeric_consist_pretty(df1, df2, key1, key2, col)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## fips (type: str)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "col = \"fips\"\n",
    "\n",
    "df1 = table1[[key1, col]].copy()\n",
    "df2 = table2[[key2, col]].copy()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### check pairwise consistency"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# merge 2 tables\n",
    "df = df1.merge(df2, left_on=key1, right_on=key2, how=\"inner\")\n",
    "\n",
    "# calculate consistency\n",
    "df['diff_temp'] = df.apply(lambda x: \"Same\" if x['%s_x' %(col)] == x['%s_y' %(col)] else \"Diff\", axis=1)\n",
    "df['diff_temp'] = df.apply(lambda x: \"Same\" if (str(x['%s_x' % (col)]) == 'nan' and str(x['%s_y' % (col)]) == 'nan') else x['diff_temp'], axis=1)\n",
    "\n",
    "corr = df[df[\"diff_temp\"] == \"Same\"].shape[0] * 1.0 / df.shape[0]\n",
    "print(\"consistency rate: \" + str(corr))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## fireplacecnt (type: numeric)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "col = \"fireplacecnt\"\n",
    "\n",
    "df1 = table1[[key1, col]].copy()\n",
    "df2 = table2[[key2, col]].copy()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### check pairwise consistency"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# merge 2 tables\n",
    "df = df1.merge(df2, left_on=key1, right_on=key2, how=\"inner\")\n",
    "df = df.dropna(how='any', subset=[col + \"_x\", col + \"_y\"]).reset_index(drop=True)\n",
    "\n",
    "corr = round(spearmanr(df[col + \"_x\"].values, df[col + \"_y\"].values)[0], 3)\n",
    "print(\"consistency rate: \" + str(corr))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### draw consistency graph"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# prepare data\n",
    "df[\"diff_temp\"] = df[col + \"_y\"] - df[col + \"_x\"]\n",
    "draw_values = df[\"diff_temp\"].dropna().values\n",
    "\n",
    "both_min = np.min([df[col + \"_x\"].min(), df[col + \"_y\"].min()])\n",
    "both_max = np.max([df[col + \"_x\"].max(), df[col + \"_y\"].max()])\n",
    "\n",
    "# draw\n",
    "plt.figure(figsize=(12, 6))\n",
    "plt.subplot(121)\n",
    "plt.scatter(df[col + \"_x\"].values, df[col + \"_y\"].values, c=TABLE1_DARK, s=5)\n",
    "plt.plot([both_min, both_max], [both_min, both_max], \"--\", c=\"#bbbbbb\")\n",
    "plt.xlim(both_min, both_max)\n",
    "plt.ylim(both_min, both_max)\n",
    "plt.title(\"corr: %.3f\" %(corr))\n",
    "\n",
    "plt.subplot(122)\n",
    "sns.distplot(draw_values, color=TABLE2_DARK)\n",
    "plt.title(\"Distribution of differences\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "#you can also use the build-in draw function\n",
    "numeric_consist_pretty(df1, df2, key1, key2, col)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## fullbathcnt (type: numeric)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "col = \"fullbathcnt\"\n",
    "\n",
    "df1 = table1[[key1, col]].copy()\n",
    "df2 = table2[[key2, col]].copy()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### check pairwise consistency"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# merge 2 tables\n",
    "df = df1.merge(df2, left_on=key1, right_on=key2, how=\"inner\")\n",
    "df = df.dropna(how='any', subset=[col + \"_x\", col + \"_y\"]).reset_index(drop=True)\n",
    "\n",
    "corr = round(spearmanr(df[col + \"_x\"].values, df[col + \"_y\"].values)[0], 3)\n",
    "print(\"consistency rate: \" + str(corr))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### draw consistency graph"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# prepare data\n",
    "df[\"diff_temp\"] = df[col + \"_y\"] - df[col + \"_x\"]\n",
    "draw_values = df[\"diff_temp\"].dropna().values\n",
    "\n",
    "both_min = np.min([df[col + \"_x\"].min(), df[col + \"_y\"].min()])\n",
    "both_max = np.max([df[col + \"_x\"].max(), df[col + \"_y\"].max()])\n",
    "\n",
    "# draw\n",
    "plt.figure(figsize=(12, 6))\n",
    "plt.subplot(121)\n",
    "plt.scatter(df[col + \"_x\"].values, df[col + \"_y\"].values, c=TABLE1_DARK, s=5)\n",
    "plt.plot([both_min, both_max], [both_min, both_max], \"--\", c=\"#bbbbbb\")\n",
    "plt.xlim(both_min, both_max)\n",
    "plt.ylim(both_min, both_max)\n",
    "plt.title(\"corr: %.3f\" %(corr))\n",
    "\n",
    "plt.subplot(122)\n",
    "sns.distplot(draw_values, color=TABLE2_DARK)\n",
    "plt.title(\"Distribution of differences\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "#you can also use the build-in draw function\n",
    "numeric_consist_pretty(df1, df2, key1, key2, col)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## garagecarcnt (type: numeric)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "col = \"garagecarcnt\"\n",
    "\n",
    "df1 = table1[[key1, col]].copy()\n",
    "df2 = table2[[key2, col]].copy()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### check pairwise consistency"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# merge 2 tables\n",
    "df = df1.merge(df2, left_on=key1, right_on=key2, how=\"inner\")\n",
    "df = df.dropna(how='any', subset=[col + \"_x\", col + \"_y\"]).reset_index(drop=True)\n",
    "\n",
    "corr = round(spearmanr(df[col + \"_x\"].values, df[col + \"_y\"].values)[0], 3)\n",
    "print(\"consistency rate: \" + str(corr))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### draw consistency graph"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# prepare data\n",
    "df[\"diff_temp\"] = df[col + \"_y\"] - df[col + \"_x\"]\n",
    "draw_values = df[\"diff_temp\"].dropna().values\n",
    "\n",
    "both_min = np.min([df[col + \"_x\"].min(), df[col + \"_y\"].min()])\n",
    "both_max = np.max([df[col + \"_x\"].max(), df[col + \"_y\"].max()])\n",
    "\n",
    "# draw\n",
    "plt.figure(figsize=(12, 6))\n",
    "plt.subplot(121)\n",
    "plt.scatter(df[col + \"_x\"].values, df[col + \"_y\"].values, c=TABLE1_DARK, s=5)\n",
    "plt.plot([both_min, both_max], [both_min, both_max], \"--\", c=\"#bbbbbb\")\n",
    "plt.xlim(both_min, both_max)\n",
    "plt.ylim(both_min, both_max)\n",
    "plt.title(\"corr: %.3f\" %(corr))\n",
    "\n",
    "plt.subplot(122)\n",
    "sns.distplot(draw_values, color=TABLE2_DARK)\n",
    "plt.title(\"Distribution of differences\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "#you can also use the build-in draw function\n",
    "numeric_consist_pretty(df1, df2, key1, key2, col)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## garagetotalsqft (type: numeric)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "col = \"garagetotalsqft\"\n",
    "\n",
    "df1 = table1[[key1, col]].copy()\n",
    "df2 = table2[[key2, col]].copy()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### check pairwise consistency"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# merge 2 tables\n",
    "df = df1.merge(df2, left_on=key1, right_on=key2, how=\"inner\")\n",
    "df = df.dropna(how='any', subset=[col + \"_x\", col + \"_y\"]).reset_index(drop=True)\n",
    "\n",
    "corr = round(spearmanr(df[col + \"_x\"].values, df[col + \"_y\"].values)[0], 3)\n",
    "print(\"consistency rate: \" + str(corr))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### draw consistency graph"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# prepare data\n",
    "df[\"diff_temp\"] = df[col + \"_y\"] - df[col + \"_x\"]\n",
    "draw_values = df[\"diff_temp\"].dropna().values\n",
    "\n",
    "both_min = np.min([df[col + \"_x\"].min(), df[col + \"_y\"].min()])\n",
    "both_max = np.max([df[col + \"_x\"].max(), df[col + \"_y\"].max()])\n",
    "\n",
    "# draw\n",
    "plt.figure(figsize=(12, 6))\n",
    "plt.subplot(121)\n",
    "plt.scatter(df[col + \"_x\"].values, df[col + \"_y\"].values, c=TABLE1_DARK, s=5)\n",
    "plt.plot([both_min, both_max], [both_min, both_max], \"--\", c=\"#bbbbbb\")\n",
    "plt.xlim(both_min, both_max)\n",
    "plt.ylim(both_min, both_max)\n",
    "plt.title(\"corr: %.3f\" %(corr))\n",
    "\n",
    "plt.subplot(122)\n",
    "sns.distplot(draw_values, color=TABLE2_DARK)\n",
    "plt.title(\"Distribution of differences\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "#you can also use the build-in draw function\n",
    "numeric_consist_pretty(df1, df2, key1, key2, col)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## heatingorsystemtypeid (type: str)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "col = \"heatingorsystemtypeid\"\n",
    "\n",
    "df1 = table1[[key1, col]].copy()\n",
    "df2 = table2[[key2, col]].copy()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### check pairwise consistency"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# merge 2 tables\n",
    "df = df1.merge(df2, left_on=key1, right_on=key2, how=\"inner\")\n",
    "\n",
    "# calculate consistency\n",
    "df['diff_temp'] = df.apply(lambda x: \"Same\" if x['%s_x' %(col)] == x['%s_y' %(col)] else \"Diff\", axis=1)\n",
    "df['diff_temp'] = df.apply(lambda x: \"Same\" if (str(x['%s_x' % (col)]) == 'nan' and str(x['%s_y' % (col)]) == 'nan') else x['diff_temp'], axis=1)\n",
    "\n",
    "corr = df[df[\"diff_temp\"] == \"Same\"].shape[0] * 1.0 / df.shape[0]\n",
    "print(\"consistency rate: \" + str(corr))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## latitude (type: numeric)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "col = \"latitude\"\n",
    "\n",
    "df1 = table1[[key1, col]].copy()\n",
    "df2 = table2[[key2, col]].copy()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### check pairwise consistency"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# merge 2 tables\n",
    "df = df1.merge(df2, left_on=key1, right_on=key2, how=\"inner\")\n",
    "df = df.dropna(how='any', subset=[col + \"_x\", col + \"_y\"]).reset_index(drop=True)\n",
    "\n",
    "corr = round(spearmanr(df[col + \"_x\"].values, df[col + \"_y\"].values)[0], 3)\n",
    "print(\"consistency rate: \" + str(corr))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### draw consistency graph"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# prepare data\n",
    "df[\"diff_temp\"] = df[col + \"_y\"] - df[col + \"_x\"]\n",
    "draw_values = df[\"diff_temp\"].dropna().values\n",
    "\n",
    "both_min = np.min([df[col + \"_x\"].min(), df[col + \"_y\"].min()])\n",
    "both_max = np.max([df[col + \"_x\"].max(), df[col + \"_y\"].max()])\n",
    "\n",
    "# draw\n",
    "plt.figure(figsize=(12, 6))\n",
    "plt.subplot(121)\n",
    "plt.scatter(df[col + \"_x\"].values, df[col + \"_y\"].values, c=TABLE1_DARK, s=5)\n",
    "plt.plot([both_min, both_max], [both_min, both_max], \"--\", c=\"#bbbbbb\")\n",
    "plt.xlim(both_min, both_max)\n",
    "plt.ylim(both_min, both_max)\n",
    "plt.title(\"corr: %.3f\" %(corr))\n",
    "\n",
    "plt.subplot(122)\n",
    "sns.distplot(draw_values, color=TABLE2_DARK)\n",
    "plt.title(\"Distribution of differences\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "#you can also use the build-in draw function\n",
    "numeric_consist_pretty(df1, df2, key1, key2, col)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## longitude (type: numeric)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "col = \"longitude\"\n",
    "\n",
    "df1 = table1[[key1, col]].copy()\n",
    "df2 = table2[[key2, col]].copy()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### check pairwise consistency"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# merge 2 tables\n",
    "df = df1.merge(df2, left_on=key1, right_on=key2, how=\"inner\")\n",
    "df = df.dropna(how='any', subset=[col + \"_x\", col + \"_y\"]).reset_index(drop=True)\n",
    "\n",
    "corr = round(spearmanr(df[col + \"_x\"].values, df[col + \"_y\"].values)[0], 3)\n",
    "print(\"consistency rate: \" + str(corr))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### draw consistency graph"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# prepare data\n",
    "df[\"diff_temp\"] = df[col + \"_y\"] - df[col + \"_x\"]\n",
    "draw_values = df[\"diff_temp\"].dropna().values\n",
    "\n",
    "both_min = np.min([df[col + \"_x\"].min(), df[col + \"_y\"].min()])\n",
    "both_max = np.max([df[col + \"_x\"].max(), df[col + \"_y\"].max()])\n",
    "\n",
    "# draw\n",
    "plt.figure(figsize=(12, 6))\n",
    "plt.subplot(121)\n",
    "plt.scatter(df[col + \"_x\"].values, df[col + \"_y\"].values, c=TABLE1_DARK, s=5)\n",
    "plt.plot([both_min, both_max], [both_min, both_max], \"--\", c=\"#bbbbbb\")\n",
    "plt.xlim(both_min, both_max)\n",
    "plt.ylim(both_min, both_max)\n",
    "plt.title(\"corr: %.3f\" %(corr))\n",
    "\n",
    "plt.subplot(122)\n",
    "sns.distplot(draw_values, color=TABLE2_DARK)\n",
    "plt.title(\"Distribution of differences\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "#you can also use the build-in draw function\n",
    "numeric_consist_pretty(df1, df2, key1, key2, col)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## lotsizesquarefeet (type: numeric)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "col = \"lotsizesquarefeet\"\n",
    "\n",
    "df1 = table1[[key1, col]].copy()\n",
    "df2 = table2[[key2, col]].copy()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### check pairwise consistency"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# merge 2 tables\n",
    "df = df1.merge(df2, left_on=key1, right_on=key2, how=\"inner\")\n",
    "df = df.dropna(how='any', subset=[col + \"_x\", col + \"_y\"]).reset_index(drop=True)\n",
    "\n",
    "corr = round(spearmanr(df[col + \"_x\"].values, df[col + \"_y\"].values)[0], 3)\n",
    "print(\"consistency rate: \" + str(corr))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### draw consistency graph"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# prepare data\n",
    "df[\"diff_temp\"] = df[col + \"_y\"] - df[col + \"_x\"]\n",
    "draw_values = df[\"diff_temp\"].dropna().values\n",
    "\n",
    "both_min = np.min([df[col + \"_x\"].min(), df[col + \"_y\"].min()])\n",
    "both_max = np.max([df[col + \"_x\"].max(), df[col + \"_y\"].max()])\n",
    "\n",
    "# draw\n",
    "plt.figure(figsize=(12, 6))\n",
    "plt.subplot(121)\n",
    "plt.scatter(df[col + \"_x\"].values, df[col + \"_y\"].values, c=TABLE1_DARK, s=5)\n",
    "plt.plot([both_min, both_max], [both_min, both_max], \"--\", c=\"#bbbbbb\")\n",
    "plt.xlim(both_min, both_max)\n",
    "plt.ylim(both_min, both_max)\n",
    "plt.title(\"corr: %.3f\" %(corr))\n",
    "\n",
    "plt.subplot(122)\n",
    "sns.distplot(draw_values, color=TABLE2_DARK)\n",
    "plt.title(\"Distribution of differences\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "#you can also use the build-in draw function\n",
    "numeric_consist_pretty(df1, df2, key1, key2, col)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## poolsizesum (type: numeric)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "col = \"poolsizesum\"\n",
    "\n",
    "df1 = table1[[key1, col]].copy()\n",
    "df2 = table2[[key2, col]].copy()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### check pairwise consistency"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# merge 2 tables\n",
    "df = df1.merge(df2, left_on=key1, right_on=key2, how=\"inner\")\n",
    "df = df.dropna(how='any', subset=[col + \"_x\", col + \"_y\"]).reset_index(drop=True)\n",
    "\n",
    "corr = round(spearmanr(df[col + \"_x\"].values, df[col + \"_y\"].values)[0], 3)\n",
    "print(\"consistency rate: \" + str(corr))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### draw consistency graph"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# prepare data\n",
    "df[\"diff_temp\"] = df[col + \"_y\"] - df[col + \"_x\"]\n",
    "draw_values = df[\"diff_temp\"].dropna().values\n",
    "\n",
    "both_min = np.min([df[col + \"_x\"].min(), df[col + \"_y\"].min()])\n",
    "both_max = np.max([df[col + \"_x\"].max(), df[col + \"_y\"].max()])\n",
    "\n",
    "# draw\n",
    "plt.figure(figsize=(12, 6))\n",
    "plt.subplot(121)\n",
    "plt.scatter(df[col + \"_x\"].values, df[col + \"_y\"].values, c=TABLE1_DARK, s=5)\n",
    "plt.plot([both_min, both_max], [both_min, both_max], \"--\", c=\"#bbbbbb\")\n",
    "plt.xlim(both_min, both_max)\n",
    "plt.ylim(both_min, both_max)\n",
    "plt.title(\"corr: %.3f\" %(corr))\n",
    "\n",
    "plt.subplot(122)\n",
    "sns.distplot(draw_values, color=TABLE2_DARK)\n",
    "plt.title(\"Distribution of differences\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "#you can also use the build-in draw function\n",
    "numeric_consist_pretty(df1, df2, key1, key2, col)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## propertycountylandusecode (type: str)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "col = \"propertycountylandusecode\"\n",
    "\n",
    "df1 = table1[[key1, col]].copy()\n",
    "df2 = table2[[key2, col]].copy()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### check pairwise consistency"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# merge 2 tables\n",
    "df = df1.merge(df2, left_on=key1, right_on=key2, how=\"inner\")\n",
    "\n",
    "# calculate consistency\n",
    "df['diff_temp'] = df.apply(lambda x: \"Same\" if x['%s_x' %(col)] == x['%s_y' %(col)] else \"Diff\", axis=1)\n",
    "df['diff_temp'] = df.apply(lambda x: \"Same\" if (str(x['%s_x' % (col)]) == 'nan' and str(x['%s_y' % (col)]) == 'nan') else x['diff_temp'], axis=1)\n",
    "\n",
    "corr = df[df[\"diff_temp\"] == \"Same\"].shape[0] * 1.0 / df.shape[0]\n",
    "print(\"consistency rate: \" + str(corr))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## propertylandusetypeid (type: str)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "col = \"propertylandusetypeid\"\n",
    "\n",
    "df1 = table1[[key1, col]].copy()\n",
    "df2 = table2[[key2, col]].copy()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### check pairwise consistency"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# merge 2 tables\n",
    "df = df1.merge(df2, left_on=key1, right_on=key2, how=\"inner\")\n",
    "\n",
    "# calculate consistency\n",
    "df['diff_temp'] = df.apply(lambda x: \"Same\" if x['%s_x' %(col)] == x['%s_y' %(col)] else \"Diff\", axis=1)\n",
    "df['diff_temp'] = df.apply(lambda x: \"Same\" if (str(x['%s_x' % (col)]) == 'nan' and str(x['%s_y' % (col)]) == 'nan') else x['diff_temp'], axis=1)\n",
    "\n",
    "corr = df[df[\"diff_temp\"] == \"Same\"].shape[0] * 1.0 / df.shape[0]\n",
    "print(\"consistency rate: \" + str(corr))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## propertyzoningdesc (type: str)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "col = \"propertyzoningdesc\"\n",
    "\n",
    "df1 = table1[[key1, col]].copy()\n",
    "df2 = table2[[key2, col]].copy()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### check pairwise consistency"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# merge 2 tables\n",
    "df = df1.merge(df2, left_on=key1, right_on=key2, how=\"inner\")\n",
    "\n",
    "# calculate consistency\n",
    "df['diff_temp'] = df.apply(lambda x: \"Same\" if x['%s_x' %(col)] == x['%s_y' %(col)] else \"Diff\", axis=1)\n",
    "df['diff_temp'] = df.apply(lambda x: \"Same\" if (str(x['%s_x' % (col)]) == 'nan' and str(x['%s_y' % (col)]) == 'nan') else x['diff_temp'], axis=1)\n",
    "\n",
    "corr = df[df[\"diff_temp\"] == \"Same\"].shape[0] * 1.0 / df.shape[0]\n",
    "print(\"consistency rate: \" + str(corr))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## rawcensustractandblock (type: key)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "col = \"rawcensustractandblock\"\n",
    "\n",
    "df1 = table1[[col]].copy()\n",
    "df2 = table2[[col]].copy()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### compare intersection"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "#nan_rate\n",
    "nan_rate1 = df1[df1[col].isnull()].shape[0] * 1.0 / df1.shape[0]\n",
    "nan_rate2 = df2[df2[col].isnull()].shape[0] * 1.0 / df2.shape[0]\n",
    "print(\"table1 nan_rate: \" + str(nan_rate1) + \"; table2 nan_rate: \" + str(nan_rate2))\n",
    "\n",
    "set_df1_col = set(df1[col].dropna().values) if nan_rate1 < 1 else set()\n",
    "set_df2_col = set(df2[col].dropna().values) if nan_rate2 < 1 else set()\n",
    "col_overlap = len(set_df1_col.intersection(set_df2_col))\n",
    "col_only_df1, col_only_df2 = len(set_df1_col - set_df2_col), len(set_df2_col - set_df1_col)\n",
    "\n",
    "print(\"col_overlap: \" + str(col_overlap) + \"; col_only_df1: \" + str(col_only_df1) + \"; col_only_df2: \" + str(col_only_df2))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### draw venn graph"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "plt.figure(figsize=(10, 5))\n",
    "venn2([set_df1_col, set_df2_col], set_labels=[\"table1\", \"table2\"], set_colors=(\"#4BACC6\", \"#F79646\"), alpha=0.8)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## regionidcity (type: str)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "col = \"regionidcity\"\n",
    "\n",
    "df1 = table1[[key1, col]].copy()\n",
    "df2 = table2[[key2, col]].copy()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### check pairwise consistency"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# merge 2 tables\n",
    "df = df1.merge(df2, left_on=key1, right_on=key2, how=\"inner\")\n",
    "\n",
    "# calculate consistency\n",
    "df['diff_temp'] = df.apply(lambda x: \"Same\" if x['%s_x' %(col)] == x['%s_y' %(col)] else \"Diff\", axis=1)\n",
    "df['diff_temp'] = df.apply(lambda x: \"Same\" if (str(x['%s_x' % (col)]) == 'nan' and str(x['%s_y' % (col)]) == 'nan') else x['diff_temp'], axis=1)\n",
    "\n",
    "corr = df[df[\"diff_temp\"] == \"Same\"].shape[0] * 1.0 / df.shape[0]\n",
    "print(\"consistency rate: \" + str(corr))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## regionidcounty (type: str)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "col = \"regionidcounty\"\n",
    "\n",
    "df1 = table1[[key1, col]].copy()\n",
    "df2 = table2[[key2, col]].copy()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### check pairwise consistency"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# merge 2 tables\n",
    "df = df1.merge(df2, left_on=key1, right_on=key2, how=\"inner\")\n",
    "\n",
    "# calculate consistency\n",
    "df['diff_temp'] = df.apply(lambda x: \"Same\" if x['%s_x' %(col)] == x['%s_y' %(col)] else \"Diff\", axis=1)\n",
    "df['diff_temp'] = df.apply(lambda x: \"Same\" if (str(x['%s_x' % (col)]) == 'nan' and str(x['%s_y' % (col)]) == 'nan') else x['diff_temp'], axis=1)\n",
    "\n",
    "corr = df[df[\"diff_temp\"] == \"Same\"].shape[0] * 1.0 / df.shape[0]\n",
    "print(\"consistency rate: \" + str(corr))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## regionidneighborhood (type: str)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "col = \"regionidneighborhood\"\n",
    "\n",
    "df1 = table1[[key1, col]].copy()\n",
    "df2 = table2[[key2, col]].copy()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### check pairwise consistency"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# merge 2 tables\n",
    "df = df1.merge(df2, left_on=key1, right_on=key2, how=\"inner\")\n",
    "\n",
    "# calculate consistency\n",
    "df['diff_temp'] = df.apply(lambda x: \"Same\" if x['%s_x' %(col)] == x['%s_y' %(col)] else \"Diff\", axis=1)\n",
    "df['diff_temp'] = df.apply(lambda x: \"Same\" if (str(x['%s_x' % (col)]) == 'nan' and str(x['%s_y' % (col)]) == 'nan') else x['diff_temp'], axis=1)\n",
    "\n",
    "corr = df[df[\"diff_temp\"] == \"Same\"].shape[0] * 1.0 / df.shape[0]\n",
    "print(\"consistency rate: \" + str(corr))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## regionidzip (type: str)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "col = \"regionidzip\"\n",
    "\n",
    "df1 = table1[[key1, col]].copy()\n",
    "df2 = table2[[key2, col]].copy()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### check pairwise consistency"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# merge 2 tables\n",
    "df = df1.merge(df2, left_on=key1, right_on=key2, how=\"inner\")\n",
    "\n",
    "# calculate consistency\n",
    "df['diff_temp'] = df.apply(lambda x: \"Same\" if x['%s_x' %(col)] == x['%s_y' %(col)] else \"Diff\", axis=1)\n",
    "df['diff_temp'] = df.apply(lambda x: \"Same\" if (str(x['%s_x' % (col)]) == 'nan' and str(x['%s_y' % (col)]) == 'nan') else x['diff_temp'], axis=1)\n",
    "\n",
    "corr = df[df[\"diff_temp\"] == \"Same\"].shape[0] * 1.0 / df.shape[0]\n",
    "print(\"consistency rate: \" + str(corr))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## roomcnt (type: numeric)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "col = \"roomcnt\"\n",
    "\n",
    "df1 = table1[[key1, col]].copy()\n",
    "df2 = table2[[key2, col]].copy()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### check pairwise consistency"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# merge 2 tables\n",
    "df = df1.merge(df2, left_on=key1, right_on=key2, how=\"inner\")\n",
    "df = df.dropna(how='any', subset=[col + \"_x\", col + \"_y\"]).reset_index(drop=True)\n",
    "\n",
    "corr = round(spearmanr(df[col + \"_x\"].values, df[col + \"_y\"].values)[0], 3)\n",
    "print(\"consistency rate: \" + str(corr))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### draw consistency graph"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# prepare data\n",
    "df[\"diff_temp\"] = df[col + \"_y\"] - df[col + \"_x\"]\n",
    "draw_values = df[\"diff_temp\"].dropna().values\n",
    "\n",
    "both_min = np.min([df[col + \"_x\"].min(), df[col + \"_y\"].min()])\n",
    "both_max = np.max([df[col + \"_x\"].max(), df[col + \"_y\"].max()])\n",
    "\n",
    "# draw\n",
    "plt.figure(figsize=(12, 6))\n",
    "plt.subplot(121)\n",
    "plt.scatter(df[col + \"_x\"].values, df[col + \"_y\"].values, c=TABLE1_DARK, s=5)\n",
    "plt.plot([both_min, both_max], [both_min, both_max], \"--\", c=\"#bbbbbb\")\n",
    "plt.xlim(both_min, both_max)\n",
    "plt.ylim(both_min, both_max)\n",
    "plt.title(\"corr: %.3f\" %(corr))\n",
    "\n",
    "plt.subplot(122)\n",
    "sns.distplot(draw_values, color=TABLE2_DARK)\n",
    "plt.title(\"Distribution of differences\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "#you can also use the build-in draw function\n",
    "numeric_consist_pretty(df1, df2, key1, key2, col)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## threequarterbathnbr (type: numeric)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "col = \"threequarterbathnbr\"\n",
    "\n",
    "df1 = table1[[key1, col]].copy()\n",
    "df2 = table2[[key2, col]].copy()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### check pairwise consistency"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# merge 2 tables\n",
    "df = df1.merge(df2, left_on=key1, right_on=key2, how=\"inner\")\n",
    "df = df.dropna(how='any', subset=[col + \"_x\", col + \"_y\"]).reset_index(drop=True)\n",
    "\n",
    "corr = round(spearmanr(df[col + \"_x\"].values, df[col + \"_y\"].values)[0], 3)\n",
    "print(\"consistency rate: \" + str(corr))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### draw consistency graph"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# prepare data\n",
    "df[\"diff_temp\"] = df[col + \"_y\"] - df[col + \"_x\"]\n",
    "draw_values = df[\"diff_temp\"].dropna().values\n",
    "\n",
    "both_min = np.min([df[col + \"_x\"].min(), df[col + \"_y\"].min()])\n",
    "both_max = np.max([df[col + \"_x\"].max(), df[col + \"_y\"].max()])\n",
    "\n",
    "# draw\n",
    "plt.figure(figsize=(12, 6))\n",
    "plt.subplot(121)\n",
    "plt.scatter(df[col + \"_x\"].values, df[col + \"_y\"].values, c=TABLE1_DARK, s=5)\n",
    "plt.plot([both_min, both_max], [both_min, both_max], \"--\", c=\"#bbbbbb\")\n",
    "plt.xlim(both_min, both_max)\n",
    "plt.ylim(both_min, both_max)\n",
    "plt.title(\"corr: %.3f\" %(corr))\n",
    "\n",
    "plt.subplot(122)\n",
    "sns.distplot(draw_values, color=TABLE2_DARK)\n",
    "plt.title(\"Distribution of differences\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "#you can also use the build-in draw function\n",
    "numeric_consist_pretty(df1, df2, key1, key2, col)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## typeconstructiontypeid (type: str)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "col = \"typeconstructiontypeid\"\n",
    "\n",
    "df1 = table1[[key1, col]].copy()\n",
    "df2 = table2[[key2, col]].copy()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### check pairwise consistency"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# merge 2 tables\n",
    "df = df1.merge(df2, left_on=key1, right_on=key2, how=\"inner\")\n",
    "\n",
    "# calculate consistency\n",
    "df['diff_temp'] = df.apply(lambda x: \"Same\" if x['%s_x' %(col)] == x['%s_y' %(col)] else \"Diff\", axis=1)\n",
    "df['diff_temp'] = df.apply(lambda x: \"Same\" if (str(x['%s_x' % (col)]) == 'nan' and str(x['%s_y' % (col)]) == 'nan') else x['diff_temp'], axis=1)\n",
    "\n",
    "corr = df[df[\"diff_temp\"] == \"Same\"].shape[0] * 1.0 / df.shape[0]\n",
    "print(\"consistency rate: \" + str(corr))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## unitcnt (type: numeric)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "col = \"unitcnt\"\n",
    "\n",
    "df1 = table1[[key1, col]].copy()\n",
    "df2 = table2[[key2, col]].copy()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### check pairwise consistency"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# merge 2 tables\n",
    "df = df1.merge(df2, left_on=key1, right_on=key2, how=\"inner\")\n",
    "df = df.dropna(how='any', subset=[col + \"_x\", col + \"_y\"]).reset_index(drop=True)\n",
    "\n",
    "corr = round(spearmanr(df[col + \"_x\"].values, df[col + \"_y\"].values)[0], 3)\n",
    "print(\"consistency rate: \" + str(corr))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### draw consistency graph"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# prepare data\n",
    "df[\"diff_temp\"] = df[col + \"_y\"] - df[col + \"_x\"]\n",
    "draw_values = df[\"diff_temp\"].dropna().values\n",
    "\n",
    "both_min = np.min([df[col + \"_x\"].min(), df[col + \"_y\"].min()])\n",
    "both_max = np.max([df[col + \"_x\"].max(), df[col + \"_y\"].max()])\n",
    "\n",
    "# draw\n",
    "plt.figure(figsize=(12, 6))\n",
    "plt.subplot(121)\n",
    "plt.scatter(df[col + \"_x\"].values, df[col + \"_y\"].values, c=TABLE1_DARK, s=5)\n",
    "plt.plot([both_min, both_max], [both_min, both_max], \"--\", c=\"#bbbbbb\")\n",
    "plt.xlim(both_min, both_max)\n",
    "plt.ylim(both_min, both_max)\n",
    "plt.title(\"corr: %.3f\" %(corr))\n",
    "\n",
    "plt.subplot(122)\n",
    "sns.distplot(draw_values, color=TABLE2_DARK)\n",
    "plt.title(\"Distribution of differences\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "#you can also use the build-in draw function\n",
    "numeric_consist_pretty(df1, df2, key1, key2, col)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## yardbuildingsqft17 (type: numeric)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "col = \"yardbuildingsqft17\"\n",
    "\n",
    "df1 = table1[[key1, col]].copy()\n",
    "df2 = table2[[key2, col]].copy()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### check pairwise consistency"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# merge 2 tables\n",
    "df = df1.merge(df2, left_on=key1, right_on=key2, how=\"inner\")\n",
    "df = df.dropna(how='any', subset=[col + \"_x\", col + \"_y\"]).reset_index(drop=True)\n",
    "\n",
    "corr = round(spearmanr(df[col + \"_x\"].values, df[col + \"_y\"].values)[0], 3)\n",
    "print(\"consistency rate: \" + str(corr))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### draw consistency graph"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# prepare data\n",
    "df[\"diff_temp\"] = df[col + \"_y\"] - df[col + \"_x\"]\n",
    "draw_values = df[\"diff_temp\"].dropna().values\n",
    "\n",
    "both_min = np.min([df[col + \"_x\"].min(), df[col + \"_y\"].min()])\n",
    "both_max = np.max([df[col + \"_x\"].max(), df[col + \"_y\"].max()])\n",
    "\n",
    "# draw\n",
    "plt.figure(figsize=(12, 6))\n",
    "plt.subplot(121)\n",
    "plt.scatter(df[col + \"_x\"].values, df[col + \"_y\"].values, c=TABLE1_DARK, s=5)\n",
    "plt.plot([both_min, both_max], [both_min, both_max], \"--\", c=\"#bbbbbb\")\n",
    "plt.xlim(both_min, both_max)\n",
    "plt.ylim(both_min, both_max)\n",
    "plt.title(\"corr: %.3f\" %(corr))\n",
    "\n",
    "plt.subplot(122)\n",
    "sns.distplot(draw_values, color=TABLE2_DARK)\n",
    "plt.title(\"Distribution of differences\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "#you can also use the build-in draw function\n",
    "numeric_consist_pretty(df1, df2, key1, key2, col)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## yardbuildingsqft26 (type: numeric)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "col = \"yardbuildingsqft26\"\n",
    "\n",
    "df1 = table1[[key1, col]].copy()\n",
    "df2 = table2[[key2, col]].copy()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### check pairwise consistency"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# merge 2 tables\n",
    "df = df1.merge(df2, left_on=key1, right_on=key2, how=\"inner\")\n",
    "df = df.dropna(how='any', subset=[col + \"_x\", col + \"_y\"]).reset_index(drop=True)\n",
    "\n",
    "corr = round(spearmanr(df[col + \"_x\"].values, df[col + \"_y\"].values)[0], 3)\n",
    "print(\"consistency rate: \" + str(corr))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### draw consistency graph"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# prepare data\n",
    "df[\"diff_temp\"] = df[col + \"_y\"] - df[col + \"_x\"]\n",
    "draw_values = df[\"diff_temp\"].dropna().values\n",
    "\n",
    "both_min = np.min([df[col + \"_x\"].min(), df[col + \"_y\"].min()])\n",
    "both_max = np.max([df[col + \"_x\"].max(), df[col + \"_y\"].max()])\n",
    "\n",
    "# draw\n",
    "plt.figure(figsize=(12, 6))\n",
    "plt.subplot(121)\n",
    "plt.scatter(df[col + \"_x\"].values, df[col + \"_y\"].values, c=TABLE1_DARK, s=5)\n",
    "plt.plot([both_min, both_max], [both_min, both_max], \"--\", c=\"#bbbbbb\")\n",
    "plt.xlim(both_min, both_max)\n",
    "plt.ylim(both_min, both_max)\n",
    "plt.title(\"corr: %.3f\" %(corr))\n",
    "\n",
    "plt.subplot(122)\n",
    "sns.distplot(draw_values, color=TABLE2_DARK)\n",
    "plt.title(\"Distribution of differences\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "#you can also use the build-in draw function\n",
    "numeric_consist_pretty(df1, df2, key1, key2, col)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## yearbuilt (type: str)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "col = \"yearbuilt\"\n",
    "\n",
    "df1 = table1[[key1, col]].copy()\n",
    "df2 = table2[[key2, col]].copy()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### check pairwise consistency"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# merge 2 tables\n",
    "df = df1.merge(df2, left_on=key1, right_on=key2, how=\"inner\")\n",
    "\n",
    "# calculate consistency\n",
    "df['diff_temp'] = df.apply(lambda x: \"Same\" if x['%s_x' %(col)] == x['%s_y' %(col)] else \"Diff\", axis=1)\n",
    "df['diff_temp'] = df.apply(lambda x: \"Same\" if (str(x['%s_x' % (col)]) == 'nan' and str(x['%s_y' % (col)]) == 'nan') else x['diff_temp'], axis=1)\n",
    "\n",
    "corr = df[df[\"diff_temp\"] == \"Same\"].shape[0] * 1.0 / df.shape[0]\n",
    "print(\"consistency rate: \" + str(corr))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## numberofstories (type: numeric)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "col = \"numberofstories\"\n",
    "\n",
    "df1 = table1[[key1, col]].copy()\n",
    "df2 = table2[[key2, col]].copy()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### check pairwise consistency"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# merge 2 tables\n",
    "df = df1.merge(df2, left_on=key1, right_on=key2, how=\"inner\")\n",
    "df = df.dropna(how='any', subset=[col + \"_x\", col + \"_y\"]).reset_index(drop=True)\n",
    "\n",
    "corr = round(spearmanr(df[col + \"_x\"].values, df[col + \"_y\"].values)[0], 3)\n",
    "print(\"consistency rate: \" + str(corr))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### draw consistency graph"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# prepare data\n",
    "df[\"diff_temp\"] = df[col + \"_y\"] - df[col + \"_x\"]\n",
    "draw_values = df[\"diff_temp\"].dropna().values\n",
    "\n",
    "both_min = np.min([df[col + \"_x\"].min(), df[col + \"_y\"].min()])\n",
    "both_max = np.max([df[col + \"_x\"].max(), df[col + \"_y\"].max()])\n",
    "\n",
    "# draw\n",
    "plt.figure(figsize=(12, 6))\n",
    "plt.subplot(121)\n",
    "plt.scatter(df[col + \"_x\"].values, df[col + \"_y\"].values, c=TABLE1_DARK, s=5)\n",
    "plt.plot([both_min, both_max], [both_min, both_max], \"--\", c=\"#bbbbbb\")\n",
    "plt.xlim(both_min, both_max)\n",
    "plt.ylim(both_min, both_max)\n",
    "plt.title(\"corr: %.3f\" %(corr))\n",
    "\n",
    "plt.subplot(122)\n",
    "sns.distplot(draw_values, color=TABLE2_DARK)\n",
    "plt.title(\"Distribution of differences\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "#you can also use the build-in draw function\n",
    "numeric_consist_pretty(df1, df2, key1, key2, col)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## structuretaxvaluedollarcnt (type: numeric)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "col = \"structuretaxvaluedollarcnt\"\n",
    "\n",
    "df1 = table1[[key1, col]].copy()\n",
    "df2 = table2[[key2, col]].copy()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### check pairwise consistency"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# merge 2 tables\n",
    "df = df1.merge(df2, left_on=key1, right_on=key2, how=\"inner\")\n",
    "df = df.dropna(how='any', subset=[col + \"_x\", col + \"_y\"]).reset_index(drop=True)\n",
    "\n",
    "corr = round(spearmanr(df[col + \"_x\"].values, df[col + \"_y\"].values)[0], 3)\n",
    "print(\"consistency rate: \" + str(corr))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### draw consistency graph"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# prepare data\n",
    "df[\"diff_temp\"] = df[col + \"_y\"] - df[col + \"_x\"]\n",
    "draw_values = df[\"diff_temp\"].dropna().values\n",
    "\n",
    "both_min = np.min([df[col + \"_x\"].min(), df[col + \"_y\"].min()])\n",
    "both_max = np.max([df[col + \"_x\"].max(), df[col + \"_y\"].max()])\n",
    "\n",
    "# draw\n",
    "plt.figure(figsize=(12, 6))\n",
    "plt.subplot(121)\n",
    "plt.scatter(df[col + \"_x\"].values, df[col + \"_y\"].values, c=TABLE1_DARK, s=5)\n",
    "plt.plot([both_min, both_max], [both_min, both_max], \"--\", c=\"#bbbbbb\")\n",
    "plt.xlim(both_min, both_max)\n",
    "plt.ylim(both_min, both_max)\n",
    "plt.title(\"corr: %.3f\" %(corr))\n",
    "\n",
    "plt.subplot(122)\n",
    "sns.distplot(draw_values, color=TABLE2_DARK)\n",
    "plt.title(\"Distribution of differences\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "#you can also use the build-in draw function\n",
    "numeric_consist_pretty(df1, df2, key1, key2, col)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## taxvaluedollarcnt (type: numeric)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "col = \"taxvaluedollarcnt\"\n",
    "\n",
    "df1 = table1[[key1, col]].copy()\n",
    "df2 = table2[[key2, col]].copy()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### check pairwise consistency"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# merge 2 tables\n",
    "df = df1.merge(df2, left_on=key1, right_on=key2, how=\"inner\")\n",
    "df = df.dropna(how='any', subset=[col + \"_x\", col + \"_y\"]).reset_index(drop=True)\n",
    "\n",
    "corr = round(spearmanr(df[col + \"_x\"].values, df[col + \"_y\"].values)[0], 3)\n",
    "print(\"consistency rate: \" + str(corr))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### draw consistency graph"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# prepare data\n",
    "df[\"diff_temp\"] = df[col + \"_y\"] - df[col + \"_x\"]\n",
    "draw_values = df[\"diff_temp\"].dropna().values\n",
    "\n",
    "both_min = np.min([df[col + \"_x\"].min(), df[col + \"_y\"].min()])\n",
    "both_max = np.max([df[col + \"_x\"].max(), df[col + \"_y\"].max()])\n",
    "\n",
    "# draw\n",
    "plt.figure(figsize=(12, 6))\n",
    "plt.subplot(121)\n",
    "plt.scatter(df[col + \"_x\"].values, df[col + \"_y\"].values, c=TABLE1_DARK, s=5)\n",
    "plt.plot([both_min, both_max], [both_min, both_max], \"--\", c=\"#bbbbbb\")\n",
    "plt.xlim(both_min, both_max)\n",
    "plt.ylim(both_min, both_max)\n",
    "plt.title(\"corr: %.3f\" %(corr))\n",
    "\n",
    "plt.subplot(122)\n",
    "sns.distplot(draw_values, color=TABLE2_DARK)\n",
    "plt.title(\"Distribution of differences\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "#you can also use the build-in draw function\n",
    "numeric_consist_pretty(df1, df2, key1, key2, col)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## assessmentyear (type: str)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "col = \"assessmentyear\"\n",
    "\n",
    "df1 = table1[[key1, col]].copy()\n",
    "df2 = table2[[key2, col]].copy()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### check pairwise consistency"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# merge 2 tables\n",
    "df = df1.merge(df2, left_on=key1, right_on=key2, how=\"inner\")\n",
    "\n",
    "# calculate consistency\n",
    "df['diff_temp'] = df.apply(lambda x: \"Same\" if x['%s_x' %(col)] == x['%s_y' %(col)] else \"Diff\", axis=1)\n",
    "df['diff_temp'] = df.apply(lambda x: \"Same\" if (str(x['%s_x' % (col)]) == 'nan' and str(x['%s_y' % (col)]) == 'nan') else x['diff_temp'], axis=1)\n",
    "\n",
    "corr = df[df[\"diff_temp\"] == \"Same\"].shape[0] * 1.0 / df.shape[0]\n",
    "print(\"consistency rate: \" + str(corr))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## landtaxvaluedollarcnt (type: numeric)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "col = \"landtaxvaluedollarcnt\"\n",
    "\n",
    "df1 = table1[[key1, col]].copy()\n",
    "df2 = table2[[key2, col]].copy()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### check pairwise consistency"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# merge 2 tables\n",
    "df = df1.merge(df2, left_on=key1, right_on=key2, how=\"inner\")\n",
    "df = df.dropna(how='any', subset=[col + \"_x\", col + \"_y\"]).reset_index(drop=True)\n",
    "\n",
    "corr = round(spearmanr(df[col + \"_x\"].values, df[col + \"_y\"].values)[0], 3)\n",
    "print(\"consistency rate: \" + str(corr))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### draw consistency graph"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# prepare data\n",
    "df[\"diff_temp\"] = df[col + \"_y\"] - df[col + \"_x\"]\n",
    "draw_values = df[\"diff_temp\"].dropna().values\n",
    "\n",
    "both_min = np.min([df[col + \"_x\"].min(), df[col + \"_y\"].min()])\n",
    "both_max = np.max([df[col + \"_x\"].max(), df[col + \"_y\"].max()])\n",
    "\n",
    "# draw\n",
    "plt.figure(figsize=(12, 6))\n",
    "plt.subplot(121)\n",
    "plt.scatter(df[col + \"_x\"].values, df[col + \"_y\"].values, c=TABLE1_DARK, s=5)\n",
    "plt.plot([both_min, both_max], [both_min, both_max], \"--\", c=\"#bbbbbb\")\n",
    "plt.xlim(both_min, both_max)\n",
    "plt.ylim(both_min, both_max)\n",
    "plt.title(\"corr: %.3f\" %(corr))\n",
    "\n",
    "plt.subplot(122)\n",
    "sns.distplot(draw_values, color=TABLE2_DARK)\n",
    "plt.title(\"Distribution of differences\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "#you can also use the build-in draw function\n",
    "numeric_consist_pretty(df1, df2, key1, key2, col)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## taxamount (type: numeric)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "col = \"taxamount\"\n",
    "\n",
    "df1 = table1[[key1, col]].copy()\n",
    "df2 = table2[[key2, col]].copy()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### check pairwise consistency"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# merge 2 tables\n",
    "df = df1.merge(df2, left_on=key1, right_on=key2, how=\"inner\")\n",
    "df = df.dropna(how='any', subset=[col + \"_x\", col + \"_y\"]).reset_index(drop=True)\n",
    "\n",
    "corr = round(spearmanr(df[col + \"_x\"].values, df[col + \"_y\"].values)[0], 3)\n",
    "print(\"consistency rate: \" + str(corr))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### draw consistency graph"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# prepare data\n",
    "df[\"diff_temp\"] = df[col + \"_y\"] - df[col + \"_x\"]\n",
    "draw_values = df[\"diff_temp\"].dropna().values\n",
    "\n",
    "both_min = np.min([df[col + \"_x\"].min(), df[col + \"_y\"].min()])\n",
    "both_max = np.max([df[col + \"_x\"].max(), df[col + \"_y\"].max()])\n",
    "\n",
    "# draw\n",
    "plt.figure(figsize=(12, 6))\n",
    "plt.subplot(121)\n",
    "plt.scatter(df[col + \"_x\"].values, df[col + \"_y\"].values, c=TABLE1_DARK, s=5)\n",
    "plt.plot([both_min, both_max], [both_min, both_max], \"--\", c=\"#bbbbbb\")\n",
    "plt.xlim(both_min, both_max)\n",
    "plt.ylim(both_min, both_max)\n",
    "plt.title(\"corr: %.3f\" %(corr))\n",
    "\n",
    "plt.subplot(122)\n",
    "sns.distplot(draw_values, color=TABLE2_DARK)\n",
    "plt.title(\"Distribution of differences\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "#you can also use the build-in draw function\n",
    "numeric_consist_pretty(df1, df2, key1, key2, col)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## taxdelinquencyyear (type: numeric)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "col = \"taxdelinquencyyear\"\n",
    "\n",
    "df1 = table1[[key1, col]].copy()\n",
    "df2 = table2[[key2, col]].copy()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### check pairwise consistency"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# merge 2 tables\n",
    "df = df1.merge(df2, left_on=key1, right_on=key2, how=\"inner\")\n",
    "df = df.dropna(how='any', subset=[col + \"_x\", col + \"_y\"]).reset_index(drop=True)\n",
    "\n",
    "corr = round(spearmanr(df[col + \"_x\"].values, df[col + \"_y\"].values)[0], 3)\n",
    "print(\"consistency rate: \" + str(corr))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### draw consistency graph"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# prepare data\n",
    "df[\"diff_temp\"] = df[col + \"_y\"] - df[col + \"_x\"]\n",
    "draw_values = df[\"diff_temp\"].dropna().values\n",
    "\n",
    "both_min = np.min([df[col + \"_x\"].min(), df[col + \"_y\"].min()])\n",
    "both_max = np.max([df[col + \"_x\"].max(), df[col + \"_y\"].max()])\n",
    "\n",
    "# draw\n",
    "plt.figure(figsize=(12, 6))\n",
    "plt.subplot(121)\n",
    "plt.scatter(df[col + \"_x\"].values, df[col + \"_y\"].values, c=TABLE1_DARK, s=5)\n",
    "plt.plot([both_min, both_max], [both_min, both_max], \"--\", c=\"#bbbbbb\")\n",
    "plt.xlim(both_min, both_max)\n",
    "plt.ylim(both_min, both_max)\n",
    "plt.title(\"corr: %.3f\" %(corr))\n",
    "\n",
    "plt.subplot(122)\n",
    "sns.distplot(draw_values, color=TABLE2_DARK)\n",
    "plt.title(\"Distribution of differences\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "#you can also use the build-in draw function\n",
    "numeric_consist_pretty(df1, df2, key1, key2, col)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## censustractandblock (type: key)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "col = \"censustractandblock\"\n",
    "\n",
    "df1 = table1[[col]].copy()\n",
    "df2 = table2[[col]].copy()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### compare intersection"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "#nan_rate\n",
    "nan_rate1 = df1[df1[col].isnull()].shape[0] * 1.0 / df1.shape[0]\n",
    "nan_rate2 = df2[df2[col].isnull()].shape[0] * 1.0 / df2.shape[0]\n",
    "print(\"table1 nan_rate: \" + str(nan_rate1) + \"; table2 nan_rate: \" + str(nan_rate2))\n",
    "\n",
    "set_df1_col = set(df1[col].dropna().values) if nan_rate1 < 1 else set()\n",
    "set_df2_col = set(df2[col].dropna().values) if nan_rate2 < 1 else set()\n",
    "col_overlap = len(set_df1_col.intersection(set_df2_col))\n",
    "col_only_df1, col_only_df2 = len(set_df1_col - set_df2_col), len(set_df2_col - set_df1_col)\n",
    "\n",
    "print(\"col_overlap: \" + str(col_overlap) + \"; col_only_df1: \" + str(col_only_df1) + \"; col_only_df2: \" + str(col_only_df2))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### draw venn graph"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "plt.figure(figsize=(10, 5))\n",
    "venn2([set_df1_col, set_df2_col], set_labels=[\"table1\", \"table2\"], set_colors=(\"#4BACC6\", \"#F79646\"), alpha=0.8)"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 2",
   "language": "python",
   "name": "python2"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 2
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython2",
   "version": "2.7.14"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}
